﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;

namespace ThirdPrize.Classes
{

    public sealed class VisitorTable : IDisposable
    {
        private readonly SqlConnection _myConn = new SqlConnection();

        public void Dispose()
        {
            _myConn.Dispose();
        }


        public VisitorTable()
        {
            _myConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            _myConn.Open();
        }

        public List<VisitInfo> GetResults()
        {
            List<VisitInfo> result = new List<VisitInfo>();

            try
            {
                string strSel = "select count(*) as num, appname, appversion, platform " +
                                "from visits " +
                                "where  dateVoted > @d " +
                                "group by appname, appversion, platform order by  appname, appversion;";
                using (var myCom1 = new SqlCommand(strSel, _myConn))
                {
                    myCom1.Parameters.AddWithValue("@d", DateTime.Now.AddMonths(-1));
                    SqlDataReader r = myCom1.ExecuteReader();

                    while (r.Read())
                    {
                        VisitInfo v = new VisitInfo();
                        v.AppName = r["appname"].ToString();
                        v.AppVersion = r["appversion"].ToString();
                        v.Count = Int32.Parse(r["num"].ToString());
                        v.Platform = r["platform"].ToString();

                        result.Add(v);
                    }
                    _myConn.Close();
                }
            }
            catch (Exception)
            {
            }
            return result;
        }
    }
}